import java.util.Scanner;
import java.sql.*;
import java.util.ArrayList;
import java.util.*;



public class BetterFuture
{
	private static String dbUsername = "yul46";
	private static String dbPassword = "3495903";
	private static int TRANS_ID = 0;
	private static String sysdate;
	
	static Scanner scan = new Scanner(System.in);
	
	private static Connection connection; //used to hold the jdbc connection to the DB
	private static Statement statement; //used to create an instance of the connection
	private static ResultSet resultSet; //used to hold the result of your query (if one exists)
	private static String query;  //this will hold the query we are using
	
	private static String login;

	public static void main (String [] args)
	{
		//connect to database
		boolean taylor = true;
		if (taylor) {
			dbUsername = "tjb44A";
			dbPassword = "3442499";
		}
		
		
		connectDB();
		
		//get system date
		getSysdate();
		setTRANS_ID();
		
		String username, password;
		boolean logged = false;
		boolean admin = false;
		
		//greet user and get login info
		System.out.println("\n\n***\tWelcome to BetterFuture!\t***\n");
		while (!logged)	{
			System.out.print("Username: ");
			username = scan.nextLine();
			System.out.print("Password: ");
			password = scan.nextLine();
			
			//check login info for account type and correctness
			if (loginAdmin(username, password))	{
				System.out.println("\nLogged in as administrator.");			
				admin = true;
				logged = true;
				login = username;
			}
			else if (loginCustomer(username, password))	{
				System.out.println("\nLogged in as customer");
				admin = false;
				logged = true;
				login = username;
			}
			else {
				System.out.println("\nBad username/password combination. Please try again.\n");
			}
		}
		
				
		while (true) {
			System.out.print("\nPress <Enter> to continue...");
			scan.nextLine();
			//display menu options
			System.out.println("\nWhat would you like to do?\n");
			
			//admin options
			if (admin) {
				System.out.println("1 - Register a New Customer");
				System.out.println("2 - Update Share Quotes");
				System.out.println("3 - Add a Mutual Fund");
				System.out.println("4 - Update the Time and Date");
				System.out.println("5 - View Statistics");
				System.out.println("0 - Exit");
			
				System.out.print("\nSelect an option: ");
				int selection = scan.nextInt();
			
				switch (selection) {
					case 0:	System.exit(0);
							break;
							
					case 1: registerCustomer();
							break;
							
					case 2: updateQuotes();
							break;
							
					case 3: addMutual();
							break;
							
					case 4: updateTime();
							break;
							
					case 5: viewStatistics();
							break;
							
					default: System.out.println("Invalid selection.\n");
							break;
				}
			}
			//customer options
			else {
				
				System.out.println("1 - Browse Mutual Funds");
				System.out.println("2 - Search for a Mutual Fund");
				System.out.println("3 - Make a Deposit");
				System.out.println("4 - Sell Shares");
				System.out.println("5 - Buy Shares");
				System.out.println("6 - Change Allocation Preference");
				System.out.println("7 - View Your Portfolio");
				System.out.println("0 - Exit");
	
				System.out.print("\nSelect an option: ");
				int selection = scan.nextInt();
			
				switch (selection) {
					case 0:	System.exit(0);
							break;
							
					case 1: browseMutual();
							break;
							
					case 2: searchMutual();
							break;
							
					case 3: makeDeposit();
							break;
							
					case 4: sellShares();
							break;
							
					case 5: buyShares();
							break;
							
					case 6: changePrefs();
							break;
							
					case 7: viewPortfolio();
							break;
							
					default: System.out.println("Invalid selection.\n");
							break;
				}
			}
			
		}//while true
		
		
		
				
	}//main

	
	// SYSTEM
	public static void connectDB() {
		System.out.println("Loading...");
		try{
			//Register the oracle driver.  This needs the oracle files provided
			//in the oracle.zip file, unzipped into the local directory and 
			//the class path set to include the local directory
			DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
			//This is the location of the database.  This is the database in oracle
			//provided to the class
			String url = "jdbc:oracle:thin:@db10.cs.pitt.edu:1521:dbclass"; 

			connection = DriverManager.getConnection(url, dbUsername, dbPassword); 
			//create a connection to DB on db10.cs.pitt.edu
			statement = connection.createStatement();

		}
		catch(Exception Ex)  //What to do with any exceptions
		{
			System.out.println("Error connecting to database.  Machine Error: " +
				Ex.toString());
			Ex.printStackTrace();
		}
	
	}	
	
	private static void getSysdate(){
		try{
			query = "SELECT to_char(c_date,'DD-MON-YYYY') FROM mutualdate";
			resultSet = statement.executeQuery(query);
			//System.out.println(resultSet.next());
			if(resultSet.next()) {
				sysdate = resultSet.getString(1);
			}
			
		}
		catch(Exception e){
			System.out.println(e);
		}
	}
	
	// SYSTEM: check for username and password combo in customer table
	public static boolean loginCustomer (String u, String p) {
		try{
			query = "SELECT * FROM CUSTOMER WHERE login = '" + u + "' AND password = '" + p + "'";
			resultSet = statement.executeQuery(query);
			//System.out.println(resultSet.next());
			if(resultSet.next()) {
				//System.out.println("Result found " + resultSet.getString(1));
				return true;
			}
			else {
				//System.out.println("not customer");
				return false;
			}
		}
		catch(Exception Ex)
		{
			System.out.println("Error: " + Ex.toString());
			return false;
		}
		
	}
	
	// SYSTEM: check for username and password combo in administrator table
	public static boolean loginAdmin (String u, String p) {
		try {
			query = "SELECT * FROM ADMINISTRATOR WHERE login = '" + u + "' AND password = '" + p + "'";
			resultSet = statement.executeQuery(query);
			if(resultSet.next()) {
				return true;
			}
			else {
				return false;
			}
		}
		catch(Exception Ex)
		{
			System.out.println("Error: " + Ex.toString());
			return false;
		}
	}
	
	
	/*********************************
	 *          admin methods        *
	 *********************************/
	
	//ADMIN: 1. register a new customer
	public static void registerCustomer() {
		String name;
		String address;
		String email;
		String login;
		String password;
		char adminAnswer;
		boolean admin;
		char infoAnswer;
		
		System.out.println("\n\t- Register a New Customer -");
		String junk = scan.nextLine();
		System.out.print("Customer preferred login: ");
		login = scan.nextLine();
		System.out.print("Customer password: ");
		password = scan.nextLine();
		System.out.print("Customer name: ");
		name = scan.nextLine();
		System.out.print("Customer address: ");
		address = scan.nextLine();
		System.out.print("Customer email address: ");
		email = scan.nextLine();
		System.out.print("Is this customer an administrator? (y/n): ");
		adminAnswer = scan.nextLine().charAt(0);
		
		if (adminAnswer == 'y' || adminAnswer == 'Y') {
			admin = true;
		}
		else if (adminAnswer == 'n' || adminAnswer == 'N') {
			admin = false;
		}
		else {
			admin = false;
		}
		
		//check if login is taken
		try {
			query = "SELECT * FROM ADMINISTRATOR WHERE login = '" + login + "'";
			resultSet = statement.executeQuery(query);
			if (resultSet.next()) {
				System.out.println("Username taken.");
				return;
			}
			else {
				query = "SELECT * FROM CUSTOMER WHERE login = '" + login + "'";
				resultSet = statement.executeQuery(query);
				if (resultSet.next()) {
					System.out.println("Username taken.");
					return;
				}
			}
		}
		catch(Exception Ex)
		{
			System.out.println("Error: " + Ex.toString());
		}
		
		System.out.println("\nHere is your new customer: ");
		System.out.println("Login username: " + login);
		System.out.println("Login password: " + password);
		System.out.println("Name: " + name);
		System.out.println("Address: " + address);
		System.out.println("Email address: " + email);
		System.out.println("Administrator: " + admin);
		System.out.print("\nIs this information correct? (y/n): ");
		infoAnswer = scan.nextLine().charAt(0);
		
		
		
		if (infoAnswer == 'y' || infoAnswer == 'Y') {
			//write to db
			query = "INSERT INTO CUSTOMER values (?,?,?,?,?,?)";
			
			try {
				PreparedStatement updateStatement = connection.prepareStatement(query);
				updateStatement.setString(1, login);
				updateStatement.setString(2, name);
				updateStatement.setString(3, email);
				updateStatement.setString(4, address);
				updateStatement.setString(5, password);
				updateStatement.setFloat(6, 0);
				
				updateStatement.executeUpdate();
				
				System.out.println("User added to database.");
			}
			catch(Exception Ex)
			{
				System.out.println("Error: " + Ex.toString());
			}
		}
		else {
			//do not write
			System.out.println("User discarded.");
		}
		
		
	}
	
	//ADMIN: 2. update share quotes for the day
	public static void updateQuotes() {
		String symbol;
		float price;
		
		System.out.println("\n\t- Update Share Quotes -");
		System.out.print("Which mutual fund would you like to update?: ");
		String junk = scan.nextLine();
		symbol = scan.nextLine();
		
		//check symbol in db
		try {
			query = "SELECT * FROM MUTUALFUND WHERE symbol = '" + symbol + "'";
			resultSet = statement.executeQuery(query);
			if (resultSet.next()) {
				//symbol exists
				System.out.print("Enter a new price for " + symbol + ": ");
				price = scan.nextFloat();
				//add price and time to table
				query = "INSERT INTO CLOSINGPRICE values('" + symbol + "'," + price + ",(SELECT * FROM MUTUALDATE))";
				resultSet = statement.executeQuery(query);
				System.out.println(symbol + " updated.");			
			}
			else {
				System.out.println("Symbol not found.");
			}
		}
		catch(Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
	}
	
	//ADMIN: 3. add a new mutual fund
	public static void addMutual() {
		String symbol;
		String name;
		String description;
		String category;
		char infoAnswer;
		
		System.out.println("\n\t- Add a New Mutual Fund -");
		System.out.print("Mutual Fund Symbol: ");
		String junk = scan.nextLine();
		symbol = scan.nextLine();
		System.out.print("Mutual Fund Name: ");
		name = scan.nextLine();
		System.out.print("Mutual Fund Description: ");
		description = scan.nextLine();
		System.out.print("Mutual Fund Category: ");
		category = scan.nextLine();
		
		System.out.println("\nHere is your new Mutual Fund:");
		System.out.println("Symbol: " + symbol);
		System.out.println("Name: " + name);
		System.out.println("Description: " + description);
		System.out.println("Category: " + category);
		System.out.print("\nIs this information correct? (y/n): ");
		infoAnswer = scan.nextLine().charAt(0);
		
		if (infoAnswer == 'y' || infoAnswer == 'Y') {
			//write to db
			query = "INSERT INTO MUTUALFUND values (?,?,?,?,(SELECT * FROM MUTUALDATE))";
			
			try {
				PreparedStatement updateStatement = connection.prepareStatement(query);
				updateStatement.setString(1, symbol);
				updateStatement.setString(2, name);
				updateStatement.setString(3, description);
				updateStatement.setString(4, category);
		
				updateStatement.executeUpdate();
				
				System.out.println("Mutual fund added to database.");
			}
			catch(Exception Ex)
			{
				System.out.println("Error: " + Ex.toString());
			}
		}
		else {
			System.out.println("Mutual Fund discarded.");
		}
	}
	
	//ADMIN: 4. update the time and date
	public static void updateTime() {
		char answer;
		int day;
		String month;
		int year;
		String date;
		
		System.out.println("\n\t- Update the Date -");
		System.out.print("Would you like to update the database date? (y/n): ");
		String junk = scan.nextLine();
		answer = scan.nextLine().charAt(0);
		if (answer == 'y' || answer == 'Y')
		{
			System.out.print("Enter date (DD-MM-YY): ");
			//junk = scan.nextLine();
			date = scan.nextLine();
				
			//update db time
			try {
				query = "DROP TABLE MUTUALDATE";
				resultSet = statement.executeQuery(query);
				query = "CREATE TABLE MUTUALDATE(c_date		date	not null, CONSTRAINT MUTUALDATE_PK PRIMARY KEY (c_date))";
				resultSet = statement.executeQuery(query);
				query = "INSERT INTO MUTUALDATE values(to_date('" + date + "','dd-mm-yy'))";
				resultSet = statement.executeQuery(query);
			
				System.out.println("Database time updated.");
			}
			catch(Exception Ex)	{
				System.out.println("Error: " + Ex.toString());
			}
		}
		else {
			System.out.println("Update discarded.");
		}
		
		try {
			query = "SELECT * FROM MUTUALDATE";
			resultSet = statement.executeQuery(query);
			resultSet.next();
			
			System.out.println("The current date is: " + resultSet.getDate(1));
		}
		catch(Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
	}
	
	//ADMIN: 5. view statistics
	public static void viewStatistics() {
		int months;
		int categories;
		int investors;
		String buffer;
		
		System.out.println("\n\t- View Statistics -");
		System.out.print("Enter number of months previous to get stats: ");
		String junk = scan.nextLine();
		months = scan.nextInt();
		System.out.print("Enter the number of highest volume categories to return: ");
		categories = scan.nextInt();
		System.out.print("Enter the number of highest investors to return: ");
		investors = scan.nextInt();
		
		//retrieve highest volume categories (highest count of shares sold):
		query = "SELECT * FROM (SELECT M.category, SUM(T.num_shares) " +
				"FROM TRXLOG T JOIN MUTUALFUND M ON T.symbol = M.symbol " +
				"WHERE T.action = 'sell' " +
				"AND T.t_date >= ADD_MONTHS((SELECT * FROM MUTUALDATE), -" + months + ") " +
				"GROUP BY M.category ORDER BY SUM(T.num_shares) DESC) " +
				"WHERE ROWNUM < " + Integer.toString(categories + 1);
				
		try {
			resultSet = statement.executeQuery(query);
			System.out.println("\nCategory   Shares Sold");
			System.out.println("--------   -----------");
			while (resultSet.next()) {
				buffer = String.format("%-11s", resultSet.getString(1));
				System.out.print(buffer);
				buffer = String.format("%-2d\n", resultSet.getInt(2));
				System.out.print(buffer);
			}
	
		}	
		catch(Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
		
		
		//retrieve investors with highest amount invested
		query = "SELECT login, SUM(O.shares * C.price) AS total " +
				"FROM OWNS O JOIN CLOSINGPRICE C ON O.symbol = C.symbol " +
				"WHERE O.symbol = C.symbol " +
				"AND p_date = (SELECT MAX(p_date) FROM CLOSINGPRICE WHERE symbol = O.symbol) " +
				"GROUP BY login";
		try {
			resultSet = statement.executeQuery(query);
			System.out.println("\nUser       Total Invested");
			System.out.println("----       --------------");
			while (resultSet.next()) {
				buffer = String.format("%-11s", resultSet.getString(1));
				System.out.print(buffer);
				buffer = String.format("%-2d\n", resultSet.getInt(2));
				System.out.print(buffer);
			}
		}
		catch (Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}

	}
	
	
	/*********************************
	 *         customer methods      *
	 *********************************/
	//CUSTOMER: 1. browse mutual funds
	public static void browseMutual() {
		String buffer;
		char sort;
		int category;
		String date;
		
		System.out.println("\n\t- Browse Mutual Funds -");
		System.out.print("What category would you like to browse?\n");
		System.out.println("1 - ALL");
		System.out.println("2 - Fixed");
		System.out.println("3 - Bonds");
		System.out.println("4 - Stocks");
		System.out.println("5 - Mixed");
		System.out.print("Choose a category: ");
		category = scan.nextInt();
		System.out.print("Sort by price? (else alphabetical) (y/n): ");
		String junk = scan.nextLine();
		sort = scan.nextLine().charAt(0);
		//construct query
		if (sort == 'y' || sort == 'Y') {
			System.out.print("Enter date you would like to view (DD-MM-YY): ");
			//junk = scan.nextLine();
			date = scan.nextLine();
		
			query = "SELECT M.symbol, M.name, M.category, E.price FROM (MUTUALFUND M JOIN (SELECT C.symbol, C.price FROM CLOSINGPRICE C JOIN MUTUALDATE D ON C.p_date=to_date('" + date + "','dd-mm-yy')) E ON M.symbol = E.symbol)";
			switch (category) {
				case 1: query += " ORDER BY E.price DESC";
						break;
				case 2: query += " WHERE M.category = 'fixed' ORDER BY E.price DESC";
						break;
				case 3: query += " WHERE M.category = 'bonds' ORDER BY E.price DESC";
						break;
				case 4: query += " WHERE M.category = 'stocks' ORDER BY E.price DESC";
						break;
				case 5: query += " WHERE M.category = 'mixed' ORDER BY E.price DESC";
						break;
				default: query += " ORDER BY E.price DESC";
						break;
			}
			
			//display results
			try {
				resultSet = statement.executeQuery(query);
				if (resultSet.next()) {
					System.out.println("");
					System.out.println("Symbol  Name                          Category          Price");
					System.out.println("------  ----------------------------  ----------------  -------");
					do {
						buffer = String.format("%-8s", resultSet.getString(1));
						System.out.print(buffer);
						buffer = String.format("%-30s", resultSet.getString(2));
						System.out.print(buffer);
						buffer = String.format("%-18s", resultSet.getString(3));
						System.out.print(buffer);
						buffer = String.format("%-10s\n", resultSet.getString(4));
						System.out.print(buffer);
					}while (resultSet.next());
				}
				else {
					System.out.println("No prices for this date.");
				}
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}


		
		
		}
		else {
			query = "SELECT symbol, name, category FROM MUTUALFUND";
			switch (category) {
				case 1: query += " ORDER BY symbol ASC";
						break;	
				case 2: query += " WHERE category = 'fixed' ORDER BY symbol ASC";
						break;
				case 3: query += " WHERE category = 'bonds' ORDER BY symbol ASC";
						break;
				case 4: query += " WHERE category = 'stocks' ORDER BY symbol ASC";
						break;
				case 5: query += " WHERE category = 'mixed' ORDER BY symbol ASC";
						break;
				default: query += " ORDER BY symbol ASC";
						break;
			}
			
			//display results
			try {
				resultSet = statement.executeQuery(query);
				if (resultSet.next()) {
					System.out.println("");
					System.out.println("Symbol  Name                          Category        ");
					System.out.println("------  ----------------------------  ----------------");
					do {
						buffer = String.format("%-8s", resultSet.getString(1));
						System.out.print(buffer);
						buffer = String.format("%-30s", resultSet.getString(2));
						System.out.print(buffer);
						buffer = String.format("%-10s\n", resultSet.getString(3));
						System.out.print(buffer);
						
					}while (resultSet.next());
				}
				else {
					System.out.println("Category does not exist.");
				}
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
		}
		
	}
	
	
	//CUSTOMER: 2. search mutual fund by text
	public static void searchMutual() {
		String search;
		String keywords[];
		String buffer;
		
		System.out.println("\n\t- Search Mutual Funds -");
		System.out.print("Enter up to two keywords separated by a space: ");
		String junk = scan.nextLine();
		search = scan.nextLine();
		//parse search into two keywords
		keywords = search.split(" ");
		
		//search db
		if (keywords[0] == null || keywords[0].equals("")) {
			query = "SELECT * FROM MUTUALFUND WHERE description = ' '";
		}
		else if (keywords.length < 2) {
			//one keyword
			query = "SELECT * FROM MUTUALFUND WHERE description LIKE '%" + keywords[0] + "%'";
		}
		else {
			//two keywords
			query = "SELECT * FROM MUTUALFUND WHERE description LIKE '%" + keywords[0] + "%' AND description LIKE '%" + keywords[1] + "%'";
		}
			
		//display results
		try {
			resultSet = statement.executeQuery(query);
			if (resultSet.next()) {
				System.out.println("");
				System.out.println("Symbol  Name                          Category          Description");
				System.out.println("------  ----------------------------  ----------------  ------------------------------------");
				do {
					buffer = String.format("%-8s", resultSet.getString(1));
					System.out.print(buffer);
					buffer = String.format("%-30s", resultSet.getString(2));
					System.out.print(buffer);
					buffer = String.format("%-18s", resultSet.getString(4));
					System.out.print(buffer);
					buffer = String.format("%-30s\n", resultSet.getString(3));
					System.out.print(buffer);
					
				}while (resultSet.next());
			}
			else {
				System.out.println("No results found.");
			}
		}
		catch(Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
	}
	
	
	
	//CUSTOMER: 3. deposit an amount for investment
	public static void makeDeposit() {
		float amount;
		
		System.out.println("\n\t- Make a Deposit -");
		System.out.print("Enter the amount you would like to deposit into your account: ");
		String junk = scan.nextLine();
		amount = scan.nextFloat();
		
		//add money, use trigger to make purchase of preferred shares
		//values('0','mike', null, '29-MAR-12','deposit', null, null, '1000');
		setTRANS_ID();
		try{
			/*query = "INSERT INTO TRXLO_temp2 values('" + 
					 TRANS_ID +"', " +
					 "'" + login +"', " +
					 "" + "null" +", " +
					 "'" + sysdate +"', " +
					 "'" + "deposit" +"', " +
					 "" + "null" +", " +
					 "" + "null" +", " +
					 "'" + (int)amount +"')" ;*/

			//query = "INSERT INTO TRXLO_temp2 " +
			//		"values(" + TRANS_ID + ",'" + login + "'" +
			//		",null,(SELECT * FROM MUTUALDATE),'deposit',null,null," + amount + ")";
		
			query = "INSERT INTO TRXLO_temp2 values(?,?,null,(SELECT * FROM MUTUALDATE),'deposit',null,null,?)";
			PreparedStatement updateStatement = connection.prepareStatement(query);
			updateStatement.setInt(1, TRANS_ID);
			updateStatement.setString(2, login);
			updateStatement.setFloat(3, amount);
			
			updateStatement.executeUpdate();

			//System.out.println(query);

		
			//resultSet = statement.executeQuery(query);
		}
		catch(Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
		scan.nextLine();
	}

	
	private static class SellShare{
		String name;
		int shares;
		int price;
		
		//public sellShare();
		
	}
	
	//CUSTOMER: 4. sell shares
	public static void sellShares() {
		String symbol;
		int number;
		String buffer;
		int index;
		ArrayList<SellShare> list = new ArrayList<SellShare>();
		SellShare share = new SellShare();
		System.out.println("\n\t- Sell Shares -");
		
		
		//check symbol against owned shares
		try{
			query  = "SELECT o.symbol, o.shares, k.price, to_char(k.p_date,'MON-DD-YYYY') " +
					 "FROM OWNS o Join (SELECT c.symbol, c.price, c.p_date " +
									   "FROM ClosingPrice c Join MutualDate m on c.p_date=m.c_date-1) k " +
								 "on o.symbol=k.symbol " +
					 "WHERE o.login='" + login + "' ";
			resultSet = statement.executeQuery(query);
			
				
			if(resultSet.next() && resultSet.getInt(2)>0 ){
				index = 0;
				
				System.out.println("\nThe Symbols of User: "+login);
				System.out.println("\n#   Symbol  Shares  ClosePrice  on  Date");
				System.out.println("--  ------  ------  ----------      -----------");
		
				
				do{
					if(resultSet.getInt(2)>0 ){
						share = new SellShare();
						buffer = String.format("%-2d  ", index+1);//index
						System.out.print(buffer);
						
						buffer = String.format("%-6s  ", resultSet.getString(1));//symbol
						share.name  = resultSet.getString(1);
						System.out.print(buffer);
						
						buffer = String.format("%-6s  ", resultSet.getString(2));//shares
						share.shares  = resultSet.getInt(2);
						System.out.print(buffer);
						
						buffer = String.format("$%-9s      ", resultSet.getString(3));//closing price
						System.out.print(buffer);
						share.price  = resultSet.getInt(3);
						buffer = String.format("%-10s\n", resultSet.getString(4));//date
						System.out.print(buffer);
						
						list.add(share);
						index++;
					}
				}while(resultSet.next());
				System.out.println("-----------------------------------------------\n");
				
			}
		
		}
		catch(Exception e){
		
			System.out.println("CUSTOMER 4:"+e.toString());
		
		}
		
		
		
		if (list.size()>0) {
			do{
				System.out.print("Enter the symbol of the shares you would like to sell: ");
				String junk = scan.nextLine();
				symbol = scan.nextLine().toUpperCase();
				System.out.print("symbol:" + symbol);
				SellShare temp=null;
				Boolean found = false;
				
				if(symbol.equals("0")){//cancelled
					break;
				}
				
				//check if symbol exists
				for(int i=0; i<list.size(); i++){
					temp = list.get(i);
					if(symbol.equals(temp.name)){
						System.out.println(" Found");
						found = true;
						break;
					}
				}
				
				
				if(found){
					
					System.out.print("Enter the number of shares to sell(1-"+temp.shares + "), 0 to Cancel:");
					number = scan.nextInt();
					//System.out.println("Number: " + number);
					
					// sell shares, add to balance via trigger
					if(number==0) {//break
						break;
					}else if(number>0 && number<temp.shares+1){
						do{
							getSysdate();
							try{
								//insert into TRXLOG(TRANS_ID, LOGIN, SYMBOL, T_DATE, ACTION, NUM_SHARES, PRICE, AMOUNT)
								//values('3','mike', 'MM', '01-APR-12','sell', '50', '15', '750');
								query = "insert into TRXLOG values ('" + TRANS_ID + "', " +
										"'" + login + "', " +
										"'" + symbol + "', " +
										"'" + sysdate + "', " +
										"'" + "sell" + "', " +
										"'" + number + "', " +
										"'" + temp.price + "', " +
										"'" + number * temp.price + "') "; 
								//System.out.println(query);
								resultSet = statement.executeQuery(query);
								TRANS_ID++;
								
								//printTRXLOG();
								printCUSTOMER(login);
								printOWNS(login);
								System.out.println("System: "+ number +" shares of "+symbol+" sold for $" + number * temp.price);
								
								scan.nextLine();
								break;
							}catch(Exception e){
								System.out.println("CUSTOMER 4:"+e.toString());
								TRANS_ID++;
							}
						}while(true);
						break;
						
					}else{
						System.out.println("Error: Out of range!");
					}
						
					
				}
				else{
					System.out.println("Error: Symbol not found!");
					
				
				}
			
			}while(true);

		}
		else {
			System.out.println("You do not own any shares.");
			scan.nextLine();
		}
		
	}
	
	private static class Symbol{
		String name;
		int price;
	}
	
	//CUSTOMER: 5. buy shares
	public static void buyShares() {
		String symbol;
		int number;
		int balance=-1;
		String buffer;
		int index;
		ArrayList<Symbol> list = new ArrayList<Symbol>();
		Symbol share = new Symbol();
	
		System.out.println("\n\t- Buy Shares -");
		
		
		try{
			query  = "SELECT m.symbol,m.name, m.category, k.price, to_char(k.p_date,'MON-DD-YYYY') " +
					 "FROM MutualFund m Join (SELECT c.symbol, c.price, c.p_date " +
											 "FROM ClosingPrice c Join MutualDate md on c.p_date=md.c_date-1) k " +
									 "on m.symbol=k.symbol  ";
			resultSet = statement.executeQuery(query);
			
			if(resultSet.next()){
				index = 0;
				
				System.out.println("\n#   Symbol  Name                            Catagory  ClosePrice  on  Date");
				  System.out.println("--  ------  ------------------------------  --------  ----------      -----------");
				  
		
				
				do{
					
					share = new Symbol();
					buffer = String.format("%-2d  ", index+1);//index
					System.out.print(buffer);
					
					buffer = String.format("%-6s  ", resultSet.getString(1));//symbol
					share.name  = resultSet.getString(1);
					System.out.print(buffer);
					
					buffer = String.format("%-30s  ", resultSet.getString(2));//name
					System.out.print(buffer);
					
					buffer = String.format("%-8s  ", resultSet.getString(3));//catagory
					System.out.print(buffer);
					
					buffer = String.format("$%-9s      ", resultSet.getString(4));//closing price
					System.out.print(buffer);
					share.price  = resultSet.getInt(4);
					
					buffer = String.format("%-10s\n", resultSet.getString(5));//date
					System.out.print(buffer);
					
					list.add(share);
					index++;
					
				}while(resultSet.next());
				System.out.println("---------------------------------------------------------------------------------");
					
			}
		}
		catch(Exception e){
			System.out.println("CUSTOMER 5:"+e.toString());
		}
		
		do{
			System.out.print("\nEnter the symbol of the shares you would like to buy, 0 to cancel: ");
			String junk = scan.nextLine();
			symbol = scan.nextLine().toUpperCase();
			System.out.print("symbol:" + symbol+" ");
			Symbol temp=null;
			Boolean found = false;
			
			if(symbol.equals("0")){//cancelled
				break;
			}
			
			//check if symbol exists
			for(int i=0; i<list.size(); i++){
				temp = list.get(i);
				//System.out.println("temp:" + temp.name + " " + temp.shares + " " + temp.price);
				if(symbol.equals(temp.name)){
					System.out.println("Found");
					found = true;
					break;
				}
			}
		
		
			
			if (found) {
				// get customer balance
				try{
					query  = "select login,balance from Customer where login='"+ login + "'";
					resultSet = statement.executeQuery(query);
					
					if(resultSet.next()){
						balance = resultSet.getInt(2);
					}
				
				}
				catch(Exception e){
					System.out.println("CUSTOMER 5:"+e.toString());
					System.out.println("Error: Cannot get customer banlance.");
					break;
				}
				
				System.out.println("User: " + login + "| Balance: " + balance);
				
				if(balance<temp.price){
					System.out.println("Error: You do not have enough balance.");
					break;
				}else{
					System.out.print("Enter the number of shares to buy(1-" + balance/temp.price+") 0-cancel: ");
					number = scan.nextInt();
					if(number==0) {//break
						break;
					}else if(number>0 && number<balance/temp.price+1){
						//
						do{
							getSysdate();
							try{
								//insert into TRXLOG(TRANS_ID, LOGIN, SYMBOL, T_DATE, ACTION, NUM_SHARES, PRICE, AMOUNT)
								//values('3','mike', 'MM', '01-APR-12','buy', '50', '15', '750');
								query = "insert into TRXLOG values ('" + TRANS_ID + "', " +
										"'" + login + "', " +
										"'" + symbol + "', " +
										"'" + sysdate + "', " +
										"'" + "buy" + "', " +
										"'" + number + "', " +
										"'" + temp.price + "', " +
										"'" + number * temp.price + "') "; 
								//System.out.println(query);
								resultSet = statement.executeQuery(query);
								TRANS_ID++;
								
								
								
								
								scan.nextLine();
								break;
							}catch(Exception e){
								System.out.println("CUSTOMER 4:"+e.toString());
								TRANS_ID++;
							}
						}while(true);
						
						//insert to OWNS
						try {
						
							//insert into Owns(LOGIN, SYMBOL, SHARES)
							//values('mike', 'RE', '50');
							query = "SELECT * FROM owns where login='"+ login + "' and symbol='"+symbol+"'";
							resultSet = statement.executeQuery(query);
							
							if (!resultSet.next()) {
								query = "insert into Owns values ('" + login+ "', " +
										"'" + symbol +"', " +
										"'" + number +"')";
								
								
								resultSet = statement.executeQuery(query);
								
							}
						}
						catch(Exception Ex) {
							System.out.println("-Error: " + Ex.toString());
						}
						
						//printTRXLOG();
						printCUSTOMER(login);
						printOWNS(login);
						System.out.println("\nSystem: "+ number +" shares of "+symbol+" bought for $" + number * temp.price);
								
						break;
						
					}else{
						System.out.println("\nError: Out of range!\n");
						
					}
				}
				
			}
			else {
				System.out.println("Symbol does not exist");
			}
		}
		while(true);
	}
	
	//CUSTOMER: 6. change allocation preference
	public static void changePrefs() {
		char choice;
		String symbol;
		int percent;
		String buffer;
		int total = 0;
		int[] percentArray = new int[10];
		String[] symbolArray = new String[10];
		int index = 0;
		int allocationNo = 0;
		boolean exists = false;
		
		
		System.out.println("\n\t- Preferred Funds -");
		//show current prefs, symbol - %
		
		query = "SELECT symbol, percentage FROM PREFERS P JOIN ALLOCATION A ON P.allocation_no = A.allocation_no "
			+ "WHERE P.allocation_no = (SELECT MAX(ALLOCATION_NO) FROM ALLOCATION WHERE login = '" + login + "')";
		try {
			resultSet = statement.executeQuery(query);
			System.out.println("Symbol	  %");
			System.out.println("------    ----");
			while (resultSet.next()) {
				buffer = String.format("%-8s  ", resultSet.getString(1));
				System.out.print(buffer);
				buffer = String.format("%-2f\n", resultSet.getFloat(2));
				System.out.print(buffer);
			}
		}
		catch (Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
		
		System.out.print("Would you like to change your allocation preferences? (y/n): ");
		String junk = scan.nextLine();
		choice = scan.nextLine().charAt(0);
		
		if (choice == 'y' || choice == 'Y') {
			while (total < 100) {
				if (index > 0) {
					System.out.println("New allocation preferences:");
					System.out.println("Symbol    %");
					System.out.println("------    ----");
					for (int i = 0; i < index; i++) {
						buffer = String.format("%-8s  %-2d\n", symbolArray[i], percentArray[i]);
						System.out.print(buffer);
					}
				}
				
				//get symbol
				System.out.print("\nEnter the symbol of the fund you prefer, 0 to stop: ");
				
				symbol = scan.nextLine().toUpperCase();
				exists = false;
				if (symbol.equals("0")) {
					break;
				}
				//check if symbol has already been entered
				for (int i = 0; i < index; i++) {
					if (symbolArray[i].equals(symbol)) {
						exists = true;
					}
				}
				if (!exists) {
					//check if symbol exists
					query = "SELECT * FROM MUTUALFUND WHERE symbol = '" + symbol + "'";
					try {
						resultSet = statement.executeQuery(query);
						if (resultSet.next()) {
							//get percentage
							System.out.print("Enter the percent to allocate (1-" + String.format("%d", 100-total) + "):");
							percent = scan.nextInt();
							if ((percent <= (100-total)) && percent > 0) {
								//percent ok
								symbolArray[index] = symbol;
								percentArray[index] = percent;
								total += percent;
								index++;

							}
							else {
								System.out.println("Invalid percentage");
							}

						}
						else {
							System.out.println("Symbol does not exist.");
						}
					}
					catch(Exception Ex) {
						System.out.println("Error: " + Ex.toString());
					}
				}
				else {
					System.out.println("Symbol already entered.");
				}
			}
			
			//get highest allocation no for increment
			query = "SELECT MAX(allocation_no) FROM ALLOCATION";
			try {
				resultSet = statement.executeQuery(query);
				if (resultSet.next()) {
					allocationNo = resultSet.getInt(1) + 1;
				}
				else {
					allocationNo = 0;
				}
			}
			catch (Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
			
			//write to allocation table
			query = "INSERT INTO ALLOCATION values(?,?,(SELECT * FROM MUTUALDATE))";
			try {
				PreparedStatement updateStatement = connection.prepareStatement(query);
				updateStatement.setInt(1, allocationNo);
				updateStatement.setString(2, login);
		
				updateStatement.executeUpdate();
			}
			catch(Exception Ex)
			{
				System.out.println("Error: " + Ex.toString());
			}
			
			
			//write to prefers table
			query = "INSERT INTO PREFERS values(?,?,?)";
			for (int i = 0; i < index; i++) {
				try {
					PreparedStatement updateStatement = connection.prepareStatement(query);
					updateStatement.setInt(1, allocationNo);
					updateStatement.setString(2, symbolArray[i]);
					updateStatement.setFloat(3, ((float)percentArray[i])/100);
					
					updateStatement.executeUpdate();
				}
				catch (Exception Ex) {
					System.out.println("Error: " + Ex.toString());
				}
			}
			//display new prefs
			query = "SELECT symbol, percentage FROM PREFERS P JOIN ALLOCATION A ON P.allocation_no = A.allocation_no "
			+ "WHERE P.allocation_no = (SELECT MAX(ALLOCATION_NO) FROM ALLOCATION WHERE login = '" + login + "')";
			try {
				resultSet = statement.executeQuery(query);
				System.out.println("New preferences written:\n");
				System.out.println("Symbol	  %");
				System.out.println("------    ----");
				while (resultSet.next()) {
					buffer = String.format("%-8s  ", resultSet.getString(1));
					System.out.print(buffer);
					buffer = String.format("%-2f\n", resultSet.getFloat(2));
					System.out.print(buffer);
				}
			}
			catch (Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
		}
		else {
			System.out.println("No changes made.");
		}
		

	}
	
	//CUSTOMER: 7. view customer portfolio
	public static void viewPortfolio() {
		int day;
		String month;
		int year;
		String date;
		String buffer;
		
		String symbolArray[] = new String[50];
		int shareArray[] = new int[50];
		float priceArray[] = new float[50];
		float currentValArray[] = new float[50];
		
		
			
		System.out.println("\n\t- Portfolio -");
		System.out.println("Enter the date you would like to view.");
		System.out.print("Enter date (DD-MM-YY): ");
		String junk = scan.nextLine();
		date = scan.nextLine();
			
		
		//get info for formatted date
		try {
			query = "SELECT O.symbol, O.shares, C.price " + 
					"FROM OWNS O JOIN CLOSINGPRICE C ON O.symbol = C.symbol " +
					"WHERE login = '" + login + "' AND C.p_date = (SELECT * FROM MUTUALDATE)";
			
			//get symbol, shares, current price
			resultSet = statement.executeQuery(query);
			int i = 0;
			while (resultSet.next()) {
				symbolArray[i] = resultSet.getString(1);
				shareArray[i] = resultSet.getInt(2);
				priceArray[i] = resultSet.getFloat(3);
				i++;
			}
			
			//get price on date given
			for (int j = 0; j < i; j++) {
				query = "SELECT price FROM CLOSINGPRICE " +
						"WHERE p_date = (to_date('" + date + "', 'dd-mm-yy')) " + 
						"AND symbol = '" + symbolArray[j] + "'";
				
				resultSet = statement.executeQuery(query);
				if (resultSet.next()) {
					currentValArray[j] = resultSet.getFloat(1) * shareArray[j];
				}
				else {
					System.out.println("No price found on " + date);
				}
			}
			
			//print some stuff
			System.out.println("Symbol    Shares    Price    Current");
			System.out.println("------    ------    -----    -------");
			for (int k = 0; k < i; k++) {
				buffer = String.format("%-11s", symbolArray[k]);
				System.out.print(buffer);
				buffer = String.format("%-11d", shareArray[k]);
				System.out.print(buffer);
				buffer = String.format("%-9f", priceArray[k]);
				System.out.print(buffer);
				buffer = String.format("%-11f\n", currentValArray[k]);
				System.out.print(buffer);
			}
				
		}
		catch (Exception Ex) {
			System.out.println("Error: " + Ex.toString());
		}
			
			
			
			

		
		
	}
	
	private static void setTRANS_ID(){
	String buffer;
		try {
				query = "SELECT max(trans_id) FROM trxlog";
				resultSet = statement.executeQuery(query);
				while (resultSet.next()) {
					TRANS_ID = resultSet.getInt(1)+1;
				}
				System.out.println("TRANS_ID to :" + TRANS_ID);
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
	
	}
	
	private static void printTRXLOG(){
	String buffer;
		try {
				query = "SELECT * FROM trxlog Order by TRANS_ID";
				resultSet = statement.executeQuery(query);
				System.out.println("");
				System.out.println("#  Login     Symbol  Date        Action      Shares  Price  Amount");
				System.out.println("-- --------  ------  ----------  ----------  ------  -----  -------");
				while (resultSet.next()) {
					buffer = String.format("%-2s ", resultSet.getInt(1)+1);
					System.out.print(buffer);
					buffer = String.format("%-8s  ", resultSet.getString(2));
					System.out.print(buffer);
					buffer = String.format("%-6s  ", resultSet.getString(3));
					System.out.print(buffer);
					buffer = String.format("%-10s  ", resultSet.getString(4).substring(0,10));
					System.out.print(buffer);
					buffer = String.format("%-10s  ", resultSet.getString(5));
					System.out.print(buffer);
					buffer = String.format("%-6s  ", resultSet.getString(6));
					System.out.print(buffer);
					buffer = String.format("%-5s  ", resultSet.getString(7));
					System.out.print(buffer);
					buffer = String.format("%-7s\n", resultSet.getString(8));
					System.out.print(buffer);
				}
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
	
	}
	
	private static void printCUSTOMER(String user){
	String buffer;
		try {
				query = "SELECT login, balance FROM Customer where login='"+user+"'";
				resultSet = statement.executeQuery(query);
				System.out.println("");
				System.out.println("User      Balance");
				System.out.println("--------  -------");
				while (resultSet.next()) {
					
					buffer = String.format("%-8s  ", resultSet.getString(1));
					System.out.print(buffer);
				
					buffer = String.format("%-8s\n", resultSet.getString(2));
					System.out.print(buffer);
				}
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
	
	}
	
	private static void printOWNS(String user){
	String buffer;
		try {
				query = "SELECT * FROM owns where login='"+user+"'";
				resultSet = statement.executeQuery(query);
				System.out.println("");
				System.out.println("User      Symbol  Shares");
				System.out.println("--------  ------  ------");
				while (resultSet.next()) {
					
					buffer = String.format("%-8s  ", resultSet.getString(1));
					System.out.print(buffer);
				
					buffer = String.format("%-6s  ", resultSet.getString(2));
					System.out.print(buffer);
					
					buffer = String.format("%-8s\n", resultSet.getString(3));
					System.out.print(buffer);
				}
			}
			catch(Exception Ex) {
				System.out.println("Error: " + Ex.toString());
			}
	
	}
}
